<?php

class hello_model extends CI_Model
{
	public $offset=0.1;
	public $offset2=0.5;
	public $table_name='hello';
	function __construct()
	{	
		parent::__construct();
		
		$config['hostname'] = 'localhost';
		$config['username'] = 'root';
		$config['password'] = '';
		$config['database'] = 'helloworld';
		$config['dbdriver'] = 'mysql';
		$config['dbprefix'] = '';
		$config['pconnect'] = TRUE;
		$config['db_debug'] = TRUE;
		$config['cache_on'] = FALSE;
		$config['cachedir'] = '';
		$config['char_set'] = 'utf8';
		$config['dbcollat'] = 'utf8_general_ci';
		$config['swap_pre'] = '';
		$config['autoinit'] = TRUE;
		$config['stricton'] = FALSE;
		//@@@@@@testers: check the database name before start!!
		$this->load->database($config);
	}
	
	
	//full functionality, @@required field name to be matched@@
	function create()
	{		
		$new_hello['userid'] = $this->session->userdata('userid');
		$new_hello['place'] = $this->input->post('location');
		$new_hello['areaid'] = $this->input->post('areaid');
		$new_hello['time'] = $this->input->post('time');
		$new_hello['date'] = $this->input->post('date');
		$new_hello['title'] = $this->input->post('title');
		$new_hello['isconfirmed'] = false;
		$insert = $this->db->insert($this->table_name, $new_hello);
		return $insert;
	}
	
	//not yet being used
	function update($profile)
	{
		$this->db->where('helloid', $profile['helloid']);
		$this->db->update($this->table_name, $profile);
	}
	//not yet being used
	function delete($profile)
	{
		$this->db->where('helloid', $profile['helloid']);
		$this->db->delete($this->table_name, $profile);
	}
	
	function get_hello_for_apply($helloid)
	{
	return $this->db->query('
		select hello.helloid, hello.title, hello.userid, hello.detail, hello.place, hello.date, hello.time, 
				hello.isconfirmed, user.fullname as ownerfullname, area.areaname, hello.guestid
		from hello, area, user
		where hello.areaid = area.areaid and user.userid = hello.userid
		and hello.helloid='.$helloid.'
		');
	}
	//too simple better not to use
	function get_hello($profile)
	{
		reset($profile);

		while (list($field, $content) = each($profile))
		{
			$this->db->where($field, $content);
		}
		$this->db->order_by("helloid", "desc");
		return $this->db->get($this->table_name);
		
	}
	
	//As the name says, I do not know I ever need it
	function ChangeQueryResultToArray($query)
	{
		if($query->num_rows() > 0)
		{
			foreach ($query->result() as $row)
			{
				$data[] = $row;
			}
			return $data;
		}
	}
	
	// this may no longer be useful, even for test
	function getAll()//For test use Only
	{
		
		return $this->db->get($this->table_name);
	}
	
	function get_hello_with_fullname($profile)
	{
		return $this->db->query('
				select hello.helloid,hello.title,hello.place,hello.date,
						hello.time,hello.created_at, user.fullname, area.areaname, hello.isconfirmed

				from hello, user, area
				where hello.userid = user.userid and area.areaid = hello.areaid
				and hello.userid = '.$profile['userid'].'
				
				order by helloid desc

				');
	}
	
	
	function get_all_with_fullname()
	{

		return $this->db->query('
				select hello.helloid,hello.title,hello.place,hello.date,
						hello.time,hello.created_at, user.fullname, area.areaname, hello.isconfirmed
				from hello, user, area
				where hello.userid = user.userid and area.areaid = hello.areaid
					 and (  hello.date >= curdate() 
							or ( hello.date = curdate() and hello.time >= curtime() )
				          )
				
				order by helloid desc
				');
	}

	function search($searchwords)
	{
		global $querystring;
		foreach($searchwords as $sw)
		{
			$word = strtolower($sw);
			$querystring = $querystring.
							' ( ( length( lower(hello.place) )-length(replace(lower(hello.place), \''.$word.'\', \'\') ) )  +
							 	( length(lower(user.fullname))-length(replace(lower(user.fullname), \''.$word.'\', \'\') ) )  +
								( length(lower(hello.title)  )-length(replace(lower(hello.title), \''.$word.'\', \'\') ) )  +
								( length(lower(area.areaname))-length(replace(lower(area.areaname), \''.$word.'\', \'\') ) )
						) / length(\''.$word.'\') + ';
		}
		
		return $this->db->query('
				select * 
				from (
					select hello.helloid, hello.title, hello.place, hello.date, hello.time,
					hello.created_at, user.fullname, area.areaname, hello.isconfirmed,'.
					$querystring.'0 as occur
					from hello, user, area
					where hello.userid=user.userid and area.areaid=hello.areaid
						  and (  hello.date >= curdate() 
							or ( hello.date = curdate() and hello.time >= curtime() )
				          )
					order by helloid
				) as result
				where occur > 0
				order by occur desc
				');
	}
	
	function get_lucky_hello($lat, $longi)
	{
		$latlow = (string)( (double)($lat)-$this->offset );
		$lathigh = (string)( (double)($lat)+$this->offset );
		$longilow = (string)( (double)($longi)-$this->offset );
		$longihigh = (string)( (double)($longi)+$this->offset );
		
		$q = $this->db->query('
					select hello.helloid
					from hello, area
					where hello.areaid = area.areaid 
					and  hello.isconfirmed = false and hello.userid <>'.$this->session->userdata('userid').'
					and '.$this->session->userdata('userid').' not in (
														select guestid from apply where apply.helloid = hello.helloid
																	      )
					and lat > '.$latlow.' and lat <'.$lathigh.' and
						longi > '.$longilow.' and longi <'.$longihigh.'
					and (  hello.date >= curdate() 
							or ( hello.date = curdate() and hello.time >= curtime() ) )
					ORDER BY RAND()
					LIMIT 1
					');
		if($q->num_rows()>0)
		{
			return $q;
		}
		else
		{
			$latlow = (string)( (double)($lat)-$this->offset2 );
			$lathigh = (string)( (double)($lat)+$this->offset2 );
			$longilow = (string)( (double)($longi)-$this->offset2 );
			$longihigh = (string)( (double)($longi)+$this->offset2 );
			
			return $this->db->query('
					select hello.helloid
					from hello, area
					where hello.areaid = area.areaid 
					and  hello.isconfirmed = false and hello.userid <>'.$this->session->userdata('userid').'
					and '.$this->session->userdata('userid').' not in (
														select guestid from apply where apply.helloid = hello.helloid
																	      )
					and lat > '.$latlow.' and lat <'.$lathigh.' and
						longi > '.$longilow.' and longi <'.$longihigh.'
					and (  hello.date >= curdate() 
							or ( hello.date = curdate() and hello.time >= curtime() ) )
					ORDER BY RAND()
					LIMIT 1
					');
		}
	}
	function get_waiting_with_fullname()
	{
		return $this->db->query('
				select hello.helloid,hello.title,hello.place,hello.date,
				hello.time,hello.created_at, user.fullname, area.areaname, hello.isconfirmed
				from hello, user, area
				where hello.userid = user.userid and area.areaid = hello.areaid
				and '.$this->session->userdata('userid').' in (
													select guestid from apply where apply.helloid = hello.helloid
														      )
				and (  hello.date >= curdate()
				or ( hello.date = curdate() and hello.time >= curtime() )
		)
	
				order by helloid desc
				');
	}
	
}


